﻿//=================================
// 数据库对象操作类
// @author:郑金泉
//=================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Obsidian.Utils;
using Obsidian.Edm;

namespace Obsidian.Data.Sql.SqlSrvClient
{
    public class SqlSrvDbHandler : DbHandler, IDbHandler
    {

        private SqlSrvExecutor executor = new SqlSrvExecutor();

        public SqlSrvDbHandler() { }

        public SqlSrvDbHandler(IModel model)
        {
            this.OperateModel = model;
        }

        private void PrepareExecutor()
        {
            executor.ConnectionString = this.OperateModel.GetDataBase().ConnectionString;
        }

        #region Insert

        public object Insert()
        {
            SqlParameter[] sqlParams;
            string sqlText = this.BuildInsertCmdText(out sqlParams);
            this.PrepareExecutor();
            object val = executor.ExecuteScalar(sqlText, sqlParams);
            return val;
        }

        private string BuildInsertCmdText(out SqlParameter[] sqlParams)
        {
            List<SqlParameter> paramList = new List<SqlParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT ");
            IModel operModel = this.OperateModel;
            DataBase db = operModel.GetDataBase();
            if (!String.IsNullOrEmpty(db.Owner))
                sb.Append("[").Append(db.Owner).Append("].");
            sb.Append("[").Append(operModel.DbTableName).Append("]");
            sb.Append(" (");
            foreach (OperateField of in this.fieldList)
            {
                DbField dbf = of.ModelField.GetDbField();
                sb.Append("[").Append(dbf.Name).Append("],");
            }
            sb.Remove(sb.Length - 1, 1).Append(") VALUES (");
            int n = 0;
            foreach (OperateField of in this.fieldList)
            {
                string paramName = "@P_" + n.ToString();
                sb.Append(paramName).Append(",");
                paramList.Add(new SqlParameter(paramName, of.DbValue));
                n++;
            }
            sb.Remove(sb.Length - 1, 1).Append(") SELECT SCOPE_IDENTITY()");
            sqlParams = paramList.ToArray();
            return sb.ToString();
        }

        #endregion

        #region Update

        public int Update()
        {
            SqlParameter[] sqlParams;
            string sqlText = this.BuildUpdateCmdText(out sqlParams);
            this.PrepareExecutor();
            int n = executor.ExecuteNonQuery(sqlText, sqlParams);
            return n;
        }

        private string BuildUpdateCmdText(out SqlParameter[] sqlParams)
        {
            List<SqlParameter> paramList = new List<SqlParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE ");
            IModel operModel = this.OperateModel;
            DataBase db = operModel.GetDataBase();
            if (!String.IsNullOrEmpty(db.Owner))
                sb.Append("[").Append(db.Owner).Append("].");
            sb.Append("[").Append(operModel.DbTableName).Append("]");
            sb.Append(" SET ");
            int n = 0;
            foreach (OperateField of in this.fieldList)
            {
                string dbFieldName = of.ModelField.GetDbField().Name;
                if (of.OperType == OperType.Not)
                {
                    string paramName = "@P_" + n.ToString();
                    sb.Append("[").Append(dbFieldName).Append("]=")
                        .Append(paramName).Append(",");
                    paramList.Add(new SqlParameter(paramName, of.DbValue));
                    n++;
                }
                else if (of.OperType == OperType.Increase)
                {
                    decimal val = Convert.ToDecimal(of.Value);
                    sb.Append(dbFieldName).Append("=")
                        .Append(dbFieldName).Append("+")
                        .Append(this.Escape(val.ToString())).Append(",");
                }
            }
            sb.Remove(sb.Length - 1, 1);

            if (this.whereCondition.SubConditions.Count > 0)
            {
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());
            }
            else
                throw new Exception("警告！为确保安全，更新操作where条件不能为空！");

            sqlParams = paramList.ToArray();
            return sb.ToString();
        }

        #endregion

        #region Select

        public bool SelectFirstTo(params IModel[] models)
        {
            DataReader dr = this.Select();
            if (dr.Read())
            {
                dr.ReadTo(models);
                dr.Close();
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataReader Select()
        {
            string sqlText = this.BuildSelectCmdText(null);
            this.PrepareExecutor();
            IDataReader dr = executor.ExecuteReader(sqlText);
            return new DataReader(dr);
        }

        public DataReader Select(QueryInfo qry)
        {
            ListAttrInfo listAttr = null;
            return this.Select(qry, out listAttr);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="qry"></param>
        /// <param name="listAttr"></param>
        /// <returns></returns>
        public DataReader Select(QueryInfo qry, out ListAttrInfo listAttr)
        {
            if (qry == null)
                throw new Exception("qry参数不能为空!");

            if (qry.PageNo.IsNull)
                throw new Exception("PageNo不能为空!");

            if (qry.PageSize.IsNull)
                throw new Exception("PageSize不能为空!");

            CheckQueryInfo(qry);

            listAttr = new ListAttrInfo();
            string sqlText = null;
            if (qry.IsPaging.isFalse() || qry.PageNo.Value == 1)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("\r\n").Append(this.BuildSelectItemsCountCmdText());
                sb.Append("\r\n").Append(this.BuildSelectCmdText(qry));
                sqlText = sb.ToString();
            }
            else
            {
                if (this.IsSqlServer2k())
                    sqlText = this.BuildPaginationCmdTextFor2K(qry);
                else
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("\r\n").Append(this.BuildSelectItemsCountCmdText());
                    sb.Append("\r\n").Append(this.BuildPaginationCmdText(qry));
                    sqlText = sb.ToString();
                }
            }
            this.PrepareExecutor();
            IDataReader dr = executor.ExecuteReader(sqlText);
            if (dr.Read())
            {
                int itemsCount = Convert.ToInt32(dr[0]);
                int pagesCount = (qry.IsPaging.isTrue()) ? MathUtil.PagesCount(qry.PageSize.Value, itemsCount)
                    : 1;
                listAttr.ItemsCount.Set(itemsCount);
                listAttr.PageNo.Set(qry.PageNo.Value);
                listAttr.PageSize.Set(qry.PageSize.Value);
                listAttr.PagesCount.Set(pagesCount);
                dr.NextResult();
            }
            else
            {
                throw new Exception("读取总数量出错！");
            }
            return new DataReader(dr);


        }

        /// <summary>
        /// 查询总数量
        /// </summary>
        /// <returns></returns>
        public int SelectItemsCount()
        {
            string sqlText = this.BuildSelectItemsCountCmdText();
            this.PrepareExecutor();
            return Convert.ToInt32(executor.ExecuteScalar(sqlText));
        }
        public int SelectItemsCount(QueryInfo qry)
        {
            ListAttrInfo listAttr = null;
            return this.SelectItemsCount(qry, out listAttr);
        }
        public int SelectItemsCount(QueryInfo qry, out ListAttrInfo listAttr)
        {
            if (qry == null)
                throw new Exception("qry参数不能为空!");

            CheckQueryInfo(qry);

            listAttr = new ListAttrInfo();
            string sqlText = null;
            if (qry.IsPaging.isFalse() || qry.PageNo.Value == 1)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("\r\n").Append(this.BuildSelectItemsCountCmdText());
                sqlText = sb.ToString();
            }
            else
            {
                if (this.IsSqlServer2k())
                    sqlText = this.BuildPaginationCmdTextFor2K(qry);
                else
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("\r\n").Append(this.BuildSelectItemsCountCmdText());
                    sqlText = sb.ToString();
                }
            }
            this.PrepareExecutor();
            IDataReader dr = executor.ExecuteReader(sqlText);
            int itemsCount = 0;
            if (dr.Read())
            {
                itemsCount = Convert.ToInt32(dr[0]);
                int pagesCount = MathUtil.PagesCount(qry.PageSize.Value, itemsCount);
                listAttr.ItemsCount.Set(itemsCount);
                listAttr.PageNo.Set(qry.PageNo.Value);
                listAttr.PageSize.Set(qry.PageSize.Value);
                listAttr.PagesCount.Set(pagesCount);
            }
            else
            {
                throw new Exception("读取总数量出错！");
            }
            return itemsCount;
        }

        private string BuildSelectItemsCountCmdText()
        {
            string joinText = this.BuildJoinText();
            string whereText = null;
            if (this.whereCondition.SubConditions.Count > 0)
                whereText = this.whereCondition.BuildSqlText();

            StringBuilder sb = new StringBuilder();
            sb.Append("\r\n").Append(" SELECT COUNT(*) FROM ");

            IModel operModel = this.OperateModel;
            DataBase db = operModel.GetDataBase();
            if (!String.IsNullOrEmpty(db.Owner))
                sb.Append("[").Append(db.Owner).Append("].");
            sb.Append("[").Append(operModel.DbTableName).Append("]");

            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);
            if (!String.IsNullOrEmpty(whereText))
                sb.Append("\r\n").Append(" WHERE ").Append(whereText);
            string sqlText = sb.ToString();
            return sqlText;
        }

        /// <summary>
        /// 构建分页查询SQL语句
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        private string BuildPaginationCmdText(QueryInfo qry)
        {
            if (qry == null)
                throw new Exception("qry参数不能为空!");

            StringBuilder sb = new StringBuilder();
            int top1 = qry.PageSize.Value;
            int top2 = qry.PageSize.Value * qry.PageNo.Value;
            int tmpNum = qry.PageSize.Value * (qry.PageNo.Value - 1);

            string orderByText = this.BuildOrderByText();
            if (String.IsNullOrEmpty(orderByText))
                throw new Exception("至少要有一个排序字段!");
            
            sb.Append("\r\n").Append("SELECT TOP ").Append(top1.ToString()).Append(" * FROM (")
                .Append("\r\n").Append("SELECT TOP ").Append(top2.ToString()).Append(" row_number() OVER(")
                .Append("\r\n").Append("ORDER BY ").Append(orderByText).Append(") AS __rownumber,");

            int n = 0;
            foreach (OperateField field in this.fieldList)
            {
                DbField dbField = field.ModelField.GetDbField();
                sb.Append("\r\n").Append(dbField.TablePrefixName).Append(" AS ")
                    .Append(dbField.QueryAlias)
                    .Append((n == this.fieldList.Count - 1) ? "" : ",");
                n++;
            }

            sb.Append("\r\n").Append(" FROM ");
            IModel operModel = this.OperateModel;
            DataBase db = operModel.GetDataBase();
            if (!String.IsNullOrEmpty(db.Owner))
                sb.Append("[").Append(db.Owner).Append("].");
            sb.Append("[").Append(operModel.DbTableName).Append("]");

            string joinText = this.BuildJoinText();
            if(!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);

            if (this.whereCondition.SubConditions.Count > 0)
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());

            sb.Append("\r\n").Append(" ORDER BY ").Append(orderByText).Append(") AS __tb ");
            sb.Append("\r\n").Append(" WHERE __rownumber > ").Append(tmpNum.ToString());

            return sb.ToString();
        }

        /// <summary>
        /// 构造 SQL Server 2000 分页查询语句
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        private string BuildPaginationCmdTextFor2K(QueryInfo qry)
        {
            int tmpTop = qry.PageSize.Value * qry.PageNo.Value;
            string pkTablePrefixName;
            if (this.OperateModel.PimaryKey == null)
                pkTablePrefixName = this.OperateModel.Fields[0].GetDbField().TablePrefixName;
            else
                pkTablePrefixName = this.OperateModel.PimaryKey.GetDbField().TablePrefixName;
            string joinText = this.BuildJoinText();
            string whereText = null;
            if (this.whereCondition.SubConditions.Count > 0)
                whereText = this.whereCondition.BuildSqlText();

            string orderByText = this.BuildOrderByText();
            if (String.IsNullOrEmpty(orderByText))
                throw new Exception("至少要有一个排序字段!");

            StringBuilder sb = new StringBuilder();
            sb.Append("\r\n").Append(" DECLARE @sqlText nvarchar(4000) ");
            sb.Append("\r\n").Append(" DECLARE @pageIndex int ");
            sb.Append("\r\n").Append(" DECLARE @pageSize int ");
            sb.Append("\r\n").Append(" DECLARE @tmpTop int ");
            sb.Append("\r\n").Append(" DECLARE @count int ");
            sb.Append("\r\n").Append(" SET @pageIndex = ").Append(qry.PageNo.Value.ToString());
            sb.Append("\r\n").Append(" SET @pageSize = ").Append(qry.PageSize.Value.ToString());
            sb.Append("\r\n").Append(" SET @tmpTop = ").Append(tmpTop.ToString());
            sb.Append("\r\n").Append(" SELECT @count = COUNT(*) FROM ").Append(this.OperateModel.GetDbTable().Name);
            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);
            if(!String.IsNullOrEmpty(whereText))
                sb.Append("\r\n").Append(" WHERE ").Append(whereText);
            sb.Append("\r\n").Append(" SELECT @count ");
            sb.Append("\r\n").Append(" IF(@count < @tmpTop) BEGIN SET @tmpTop = @count SET @pageSize = @count % @pageSize END ");
            sb.Append("\r\n").Append(" SET @sqlText = 'SELECT * FROM (SELECT ' + ");
            sb.Append("\r\n").Append("'TOP ' + str(@pageSize) + ' ' + ");
            int n = 0;
            foreach (OperateField field in this.fieldList)
            {
                DbField dbField = field.ModelField.GetDbField();
                sb.Append("\r\n").Append("'").Append(dbField.Name).Append(" AS ").Append(dbField.QueryAlias)
                    .Append((n == this.fieldList.Count - 1) ? "' + " : ",' + ");
                n++;
            }
            sb.Append("\r\n").Append("' FROM [").Append(this.OperateModel.GetDbTable().Name).Append("]' + ");
            sb.Append("\r\n").Append("' WHERE ").Append(pkTablePrefixName).Append(" IN ( ' + ");
            sb.Append("\r\n").Append("' SELECT TOP ' + str(@tmpTop) + ' ").Append(pkTablePrefixName).Append("' + ");
            sb.Append("\r\n").Append("' FROM [").Append(this.OperateModel.GetDbTable().Name).Append("]' + ");
            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText.Replace("'", "''"));
            if (!String.IsNullOrEmpty(whereText))
                sb.Append("\r\n").Append("' WHERE ").Append(whereText.Replace("'", "''")).Append("' + ");
            sb.Append("\r\n").Append("' ORDER BY ").Append(orderByText).Append("' + ");

            sb.Append("\r\n").Append("' ) ORDER BY ")
                .Append(orderByText.Replace("ASC", "ASC_DESC").Replace(" DESC", " ASC").Replace("ASC_", ""))
                .Append("' + ");

            StringBuilder sbOrder = new StringBuilder();
            foreach (OrderByClause clause in this.orderByList)
            {
                sbOrder.Append("__temp.").Append(clause.Field.GetDbField().QueryAlias).Append(" ")
                    .Append(clause.Type == OrderType.ASC ? "ASC " : "DESC ");
            }

            sb.Append("\r\n").Append("') AS __temp ORDER BY ").Append(sbOrder.ToString()).Append("' ");
            sb.Append("\r\n").Append("exec(@sqlText)");
            return sb.ToString();
        }

        /// <summary>
        /// 构造查询语句
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        private string BuildSelectCmdText(QueryInfo qry)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");

            if (qry != null && qry.IsPaging.isTrue() && qry.PageNo.Value == 1)
                sb.Append("TOP ").Append(qry.PageSize.Value.ToString()).Append(" ");

            int n = 0;
            foreach (OperateField field in this.fieldList)
            {
                DbField dbField = field.ModelField.GetDbField();
                if (dbField != null)
                {
                    sb.Append("\r\n").Append(dbField.TablePrefixName).Append(" AS ").Append(dbField.QueryAlias)
                        .Append((n == this.fieldList.Count - 1) ? "" : ",");
                    n++;
                }
            }
            sb.Append("\r\n FROM ");
            IModel operModel = this.OperateModel;
            DataBase db = operModel.GetDataBase();
            if (!String.IsNullOrEmpty(db.Owner))
                sb.Append("[").Append(db.Owner).Append("].");
            sb.Append("[").Append(operModel.DbTableName).Append("]");

            string joinText = this.BuildJoinText();
            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);
            
            if(this.whereCondition.SubConditions.Count > 0)
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());

            string orderByStr = this.BuildOrderByText();
            if (!String.IsNullOrEmpty(orderByStr))
                sb.Append("\r\n").Append(" ORDER BY ").Append(orderByStr);
                
            return sb.ToString();
        }

        #endregion

        #region Delete

        public int Delete()
        {
            string sqlText = this.BuildDeleteCmdText();
            this.PrepareExecutor();
            int n = executor.ExecuteNonQuery(sqlText);
            return n;
        }

        private string BuildDeleteCmdText()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE ");
            IModel operModel = this.OperateModel;
            DataBase db = operModel.GetDataBase();
            if (!String.IsNullOrEmpty(db.Owner))
                sb.Append("[").Append(db.Owner).Append("].");
            sb.Append("[").Append(operModel.DbTableName).Append("]");

            string joinText = this.BuildJoinText();
            if (!String.IsNullOrEmpty(joinText))
            {
                sb.Append(" FROM [")
                    .Append(this.OperateModel.GetDbTable().Name)
                    .Append("]");
                sb.Append("\r\n").Append(joinText);
            }

            if (this.whereCondition.SubConditions.Count > 0)
            {
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());
            }
            else
                throw new Exception("警告！为确保安全，更新操作where条件不能为空！");
            return sb.ToString();
        }

        #endregion

        #region

        private string BuildJoinText()
        {
            if (this.dictJoinClauses.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (string key in this.dictJoinClauses.Keys)
                {
                    JoinClause joinClause = dictJoinClauses[key];
                    sb.Append(joinClause.BuildSqlText());
                }
                return sb.ToString();
            }
            else
            {
                return null;
            }
        }

        public IDbHandler OrderBy(IModelField field)
        {
            OrderByClause clause = new OrderByClause(field, OrderType.ASC);
            this.orderByList.Add(clause);
            return this;
        }

        public IDbHandler OrderBy(IModelField field, OrderType type)
        {
            OrderByClause clause = new OrderByClause(field, type);
            this.orderByList.Add(clause);
            return this;
        }

        private string BuildOrderByText()
        {
            if (this.orderByList.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (OrderByClause clause in this.orderByList)
                    sb.Append(clause.BuildSqlText()).Append(",");
                sb.Remove(sb.Length - 1, 1);
                return sb.ToString();
            }
            else
            {
                return null;
            }
        }

        private int _isSqlServer2k = -1;
        private bool IsSqlServer2k()
        {
            if (this._isSqlServer2k == -1)
            {
                string dbVersion = this.OperateModel.GetDataBase().Version;
                if (dbVersion.IndexOf("Microsoft SQL Server  2000") >= 0)
                    this._isSqlServer2k = 1;
                else
                    this._isSqlServer2k = 0;
            }
            return this._isSqlServer2k == 1;
        }

        #endregion

    }
}
